﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Word;
using Application=System.Windows.Forms.Application;

namespace SetLanguageInfo
{
    public partial class DBDataConvert : Form
    {
        public DBDataConvert()
        {
            InitializeComponent();
        }

        DataTable GetDataTableBySql(string sql)
        {
            SqlConnection conn = new SqlConnection(textBox1.Text);
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql,conn);
            try
            {
                da.Fill(dt);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        private void btnDataConvert_Click(object sender, EventArgs e)
        {
            string sql = @"select o.name tableName,c.name columnName,b.keyColName from sys.all_columns c
inner join sys.all_objects o on c.object_id=o.object_id and o.type='U' and c.Name<>'ImportNum'
and o.Name<>'sysdiagrams' 
inner join (select distinct o.name tableName,c.Name keyColName from sys.all_columns c
inner join sys.all_objects o on c.object_id=o.object_id and o.type='U' 
and (c.Name='Id' or c.Name = 'HId')
) b on b.tableName=o.Name
inner join systypes t on t.xtype=c.system_type_id and (
t.name = 'nvarchar' or t.name = 'varchar' or t.name='char'
 or t.name='nchar'
) order by o.Name,c.Name";

            DataTable dtTableNames = GetDataTableBySql(sql);
            StringCollection scColNames = new StringCollection();
            
            StringBuilder sb = new StringBuilder();
            string strSqlHead = "";
            string strSqlLast = "";string pevSql = "";
            _Application objWord = new Microsoft.Office.Interop.Word.Application();
            object t = Missing.Value;
            object nt = Missing.Value;
            object dt = Missing.Value;
            object v = true;
            Document objDocument = objWord.Documents.Add(ref t, ref nt, ref dt, ref v);
            string strFileFullName = "c:\\test.sql";
            if(File.Exists(strFileFullName)) File.Delete(strFileFullName);
            //FileStream fs = new FileStream(strFileFullName, FileMode.Append);
            //StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
            LanguageHelper langHelper = new LanguageHelper();
            StringCollection scExecSql = new StringCollection();
            int rowAmount = 0;
            try
            {
                
                for (int k=0;k<dtTableNames.Rows.Count;k++)
                {
                    DataRow dr = dtTableNames.Rows[k];
                    string strTableName = Convert.ToString(dr["tableName"]);
                    string strColumnName = Convert.ToString(dr["columnName"]);
                    string strKeyColName = Convert.ToString(dr["KeyColName"]);
                   
                    if (scColNames.Contains(strTableName))
                    {
                        sb.Append("," + strColumnName + "='''+isnull(" + strColumnName + ",'')+'''");
                    }
                    else
                    { 
                        StringBuilder sbResult = new StringBuilder();
                        if (sb.Length > 0)
                        {
                            pevSql = strSqlHead + sb + strSqlLast;
                            DataTable tempTableSqlData = GetDataTableBySql(pevSql);
                           
                            for(int i=0;i<tempTableSqlData.Rows.Count;i++)
                            {
                                DataRow tempDr = tempTableSqlData.Rows[i];
                                rowAmount++;
                                sbResult.Append(tempDr[0] + ";");
                                
                                //if (rowAmount % 1000 == 0)
                                //    sbResult.Append("go\r\n");
                                if(sbResult.Length>18000 || (i+1)==tempTableSqlData.Rows.Count)
                                {
                                    string strInfo = langHelper.LangConvert(sbResult.ToString(), objWord, cboConvertType.Text);
                                    //sw.Write(strInfo);
                                    scExecSql.Add(strInfo);
                                    sbResult = new StringBuilder();
                                }
                            }
                        }
                        sb = new StringBuilder();
                        pevSql = "";
                        sb.Append(strColumnName + "='''+isnull(" + strColumnName + ",'')+'''");
                        strSqlHead = "Select 'update " + strTableName + " set ";
                        strSqlLast = " where "+strKeyColName+"='''+Cast(" + strKeyColName + " as varchar(50))+'''' from " + strTableName;
                        scColNames.Add(strTableName);
                    }
                    if((k+1) == dtTableNames.Rows.Count)
                    {
                        StringBuilder sbResult = new StringBuilder();
                        if (sb.Length > 0)
                        {
                            pevSql = strSqlHead + sb + strSqlLast;
                            DataTable tempTableSqlData = GetDataTableBySql(pevSql);

                            for (int i = 0; i < tempTableSqlData.Rows.Count; i++)
                            {
                                DataRow tempDr = tempTableSqlData.Rows[i];
                                rowAmount++;
                                sbResult.Append(tempDr[0] + ";");

                                //if (rowAmount % 1000 == 0)
                                //    sbResult.Append("go\r\n");
                                if (sbResult.Length > 18000 || (i + 1) == tempTableSqlData.Rows.Count)
                                {
                                    string strInfo = langHelper.LangConvert(sbResult.ToString(), objWord, cboConvertType.Text);
                                    //sw.Write(strInfo);
                                    scExecSql.Add(strInfo);
                                    sbResult = new StringBuilder();
                                }
                            }
                        }
                    }
                }
            }
            finally
            {
                object sc = 0;
                object of = Missing.Value;
                object rd = Missing.Value;
                objWord.Quit(ref sc, ref of, ref rd);
                objDocument = null;
                objWord = null;
                //sw.Close();
                //fs.Close();
            }

            SqlConnection conn = new SqlConnection(textBox1.Text);
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandTimeout = 900;
            try
            {
                conn.Open();
                foreach(string str in scExecSql)
                {
                    comm.CommandText = str;
                    comm.ExecuteNonQuery();
                }
            }
            finally
            {
                conn.Close();
            }

            MessageBox.Show("OK");
        }

    }
}
